package com.example.authsystem.repository;

import com.example.authsystem.entity.AuditLog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Repository
public interface AuditLogRepository extends JpaRepository<AuditLog, Long> {

    // 按用户名查询
    Page<AuditLog> findByUsernameContainingIgnoreCase(String username, Pageable pageable);

    // 按操作类型查询
    Page<AuditLog> findByAction(String action, Pageable pageable);

    // 按时间范围查询
    Page<AuditLog> findByTimestampBetween(LocalDateTime startTime, LocalDateTime endTime, Pageable pageable);

    // 组合条件查询
    @Query("SELECT a FROM AuditLog a WHERE " +
            "(:action IS NULL OR a.action = :action) AND " +
            "(:username IS NULL OR a.username LIKE %:username%) AND " +
            "(:startTime IS NULL OR a.timestamp >= :startTime) AND " +
            "(:endTime IS NULL OR a.timestamp <= :endTime)")
    Page<AuditLog> findByConditions(@Param("action") String action,
                                    @Param("username") String username,
                                    @Param("startTime") LocalDateTime startTime,
                                    @Param("endTime") LocalDateTime endTime,
                                    Pageable pageable);

    // 统计查询
    @Query("SELECT COUNT(a) FROM AuditLog a WHERE a.action = 'USER_LOGIN'")
    long countTotalLogins();

    @Query("SELECT COUNT(a) FROM AuditLog a WHERE a.action = 'USER_LOGIN' AND DATE(a.timestamp) = :date")
    long countLoginsByDate(@Param("date") LocalDate date);

    @Query("SELECT COUNT(DISTINCT a.username) FROM AuditLog a WHERE DATE(a.timestamp) = :date")
    long countActiveUsersByDate(@Param("date") LocalDate date);

    @Query("SELECT a.action as action, COUNT(a) as count FROM AuditLog a " +
            "WHERE a.timestamp >= :startTime GROUP BY a.action")
    List<Map<String, Object>> getActionStatistics(@Param("startTime") LocalDateTime startTime);

    // 按IP地址查询（安全监控）
    List<AuditLog> findByIpAddressAndTimestampAfter(String ipAddress, LocalDateTime after);

    // 失败操作查询
    Page<AuditLog> findBySuccessFalseOrderByTimestampDesc(Pageable pageable);

    // 清理过期日志
    @Query("DELETE FROM AuditLog a WHERE a.timestamp < :cutoffTime")
    int deleteByTimestampBefore(@Param("cutoffTime") LocalDateTime cutoffTime);

    // 按目标用户查询（用于查看对特定用户的操作）
    Page<AuditLog> findByTargetUserIdOrderByTimestampDesc(Long targetUserId, Pageable pageable);

    // 获取最近的登录记录
    @Query("SELECT a FROM AuditLog a WHERE a.action = 'USER_LOGIN' AND a.username = :username " +
            "ORDER BY a.timestamp DESC")
    List<AuditLog> findRecentLoginsByUsername(@Param("username") String username, Pageable pageable);

    // 统计每日活跃度
    @Query("SELECT DATE(a.timestamp) as date, COUNT(DISTINCT a.username) as activeUsers " +
            "FROM AuditLog a WHERE a.timestamp BETWEEN :startTime AND :endTime " +
            "GROUP BY DATE(a.timestamp) ORDER BY date")
    List<Map<String, Object>> getDailyActivityStats(@Param("startTime") LocalDateTime startTime,
                                                    @Param("endTime") LocalDateTime endTime);
}
